package com.east.sng.dao;

import com.east.sng.entity.param.StuParam;
import com.east.sng.entity.param.StuSearchParam;
import com.east.sng.entity.result.StuCourseFind;
import com.east.sng.entity.result.StuFind;
import com.east.sng.entity.result.StuInfo;
import com.east.sng.entity.result.StuSearchFind;
import com.east.sng.utils.RedisCache;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author east
 * @date: 2021/12/31
 * @Description:
 **/
@Repository
@CacheNamespace(implementation = RedisCache.class)
public interface StuDao {

    /**
     * 查询全部学生信息
     * @param params 参数列表
     * @return 学生实例对象列表
     */
    List<StuInfo> selectStus(@Param("params") StuSearchParam params);

    /**
     * 根据学生姓名查询学生的名字,监护人姓名和监护人电话
     * @return 学生id和名字列表
     */
    @Select("select stu_id stuId,stu_name stuName,stu_parent_name stuParentName,stu_parent_phone stuParentPhone stuName from stu where flag = 0 and stu_name = #{stuName} ")
    List<StuSearchFind> selectStuColumns(String stuName);

    /**
     * 根据id查询学生信息
     * @param stuId 学生id
     * @return 学生实例对象
     */
    @Select("select stu.* from stu " +
            "where flag = 0 and stu.stu_id = #{stuId} ")
    StuFind selectStu(Integer stuId);

    /**
     * 添加学生信息
     * @param stu 学生实例对象
     */
    @Insert("insert into stu(stu_name, stu_parent_name, stu_parent_relation, stu_sex, stu_age, stu_address, stu_parent_phone) " +
            "values (#{stu.stuName}, #{stu.stuParentName}, #{stu.stuParentRelation}, #{stu.stuSex}, #{stu.stuAge},  #{stu.stuAddress}, #{stu.stuParentPhone}, #{stu.courseName}) ")
    void insertStu(@Param("stu") StuParam stu);

    /**
     * 修改学生信息
     * @param stu 学生实例对象
     */
    @Update("update stu set " +
            "stu_name = #{stu.stuName}," +
            "stu_parent_name = #{stu.stuParentName}," +
            "stu_parent_relation = #{stu.stuParentRelation}," +
            "stu_sex = #{stu.stuSex}," +
            "stu_age = #{stu.stuAge}," +
            "stu_address = #{stu.stuAddress}," +
            "stu_parent_phone = #{stu.stuParentPhone}, " +
            "remark = #{stu.remark}, " +
            "course_id = #{stu.courseName} " +
            "where stu_id = #{stu.stuId}")
    void updateStu(@Param("stu") StuParam stu);

    /**
     * 删除或恢复学生信息
     * @param stuId 学生id
     * @param flag 删除标志
     * @return 受影响行数
     */
    @Delete("update stu set flag = #{flag} where stu_id = #{stuId} ")
    Integer deleteOrRecoverStu(@Param("stuId") Integer stuId,@Param("flag") Integer flag);

    /**
     * 批量删除或恢复学生信息
     * @param stuIds 学生id列表
     * @param flag 删除标志
     * @return 受影响行数
     */
    @Delete({"<script>","update stu set flag = #{flag} where stu_id in",
            "<foreach collection='list' item='stuId' open='(' separator=',' close=')'>",
            "#{stuId}",
            "</foreach>",
            "</script>"})
    Integer batchDeleteOrRecoverStus(@Param("list") List<Integer> stuIds,@Param("flag") Integer flag);

    /**
     * 根据学生id查询学生课程信息
     * @param stuId 学生id
     * @return 学生课程信息实例对象
     */
    @Select("SELECT c.course_name,c.course_price,c.course_duration,t.teacher_name,cr.classroom_name " +
            "FROM stu LEFT JOIN course c " +
            "LEFT JOIN teacher t ON c.teacher_id = t.teacher_id " +
            "LEFT JOIN classroom cr ON c.classroom_id = cr.classroom_id " +
            "ON stu.course_id = c.course_id " +
            "WHERE stu.flag = 0 and stu_id = #{stuId}")
    StuCourseFind selectStuCourseInfo(Integer stuId);

    /**
     * 根据学生id列表查询对应的学生信息
     * @param stuIds 学生id列表
     * @return 学生信息实例对象列表
     */
    @Select({"<script>","select stu.stu_id, stu.stu_name, stu.stu_parent_name, stu.stu_parent_relation,",
            "stu.stu_sex, stu.stu_age, stu.stu_address, stu.stu_parent_phone, stu.remark,",
            "c.course_name,ISNULL(cg.charge_id) payer_id from stu ",
            "left join course c on c.course_id = stu.course_id ",
            "left join charge cg on cg.stu_id = stu.stu_id ",
            "where stu.flag = 0 and stu.stu_id in",
            "<foreach collection='list' item='stuId' open='(' separator=',' close=')'>",
            "#{stuId}",
            "</foreach>",
            "</script>"})
    List<StuInfo> selectStuInfo(List<Integer> stuIds);
}